package com.thinkingstar.iads.datacenter.entity.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import javax.jcr.query.Query;

import com.thinkingstar.iads.common.utils.StringUtil;
import com.thinkingstar.iads.datacenter.entity.DcCRHInfo;
import org.apache.commons.lang3.StringUtils;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.QueryResult;
import org.nutz.dao.Sqls;
import org.nutz.dao.pager.Pager;
import org.nutz.dao.sql.Criteria;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.nutz.ioc.loader.annotation.IocBean;
import org.nutz.lang.util.NutMap;

import com.thinkingstar.iads.common.serviceImpl.EntityRepositoryServiceImpl;
import com.thinkingstar.iads.common.utils.CommonDateUtil;
import com.thinkingstar.iads.datacenter.entity.DcEquipmentManufacturer;
import com.thinkingstar.iads.datacenter.entity.DcManufacturerEquipment;

/**
 * @ClassName: DaoDcProjectDesignContent 
 * @Description:数据中心_项目_厂商设备维护
 * @author 1157372219@qq.com 
 * @date 2018年3月19日   
 */
@IocBean(args = {"refer:dao"})
public class DaoDcManufacturerEquipment extends EntityRepositoryServiceImpl<DcManufacturerEquipment>{

	public DaoDcManufacturerEquipment(Dao dao) {
		super(dao);
	}
	
	//根据设备id以及厂商id查询设备
	public DcManufacturerEquipment findObjByEquipmentIdAndManufacturerId(String equipment_id , String manufacturer_id){
		Criteria criteria = Cnd.NEW();
		criteria.where().and("del_flag", "=", false).and("equipment_id", "=", equipment_id).and("equipment_manufacturer_id", "=", manufacturer_id);
		List <DcManufacturerEquipment> query_list = query(criteria);
		if(!query_list.isEmpty()){
			return query_list.get(0);
		}
		return null;
	}
	
	//查询某种设备的所有厂商
	public List<DcManufacturerEquipment> findManufacturerByEquipmentId(String equipment_id){
		Criteria criteria = Cnd.NEW();
		criteria.where().and("del_flag", "=", false).and("equipment_id", "=", equipment_id);
		return query(criteria);
	}
	
	//根据设备id查询生成此设备的厂商
	public List<NutMap> findManufacturerListByEquipmentId(String equipment_id){
		String sql = " select distinct(equipment_manufacturer_id) equipment_manufacturer_id , equipment_manufacturer_name  "
				+ " from dc_manufacturer_equipment where del_flag = '0' "
				+ " and equipment_id = @equipment_id ";
		Sql find_sql = Sqls.create(sql);
		find_sql.params().set("equipment_id", equipment_id);
		find_sql.setCallback(new SqlCallback() {
			List <NutMap> list = new LinkedList<>();
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				while (rs.next()) {
					NutMap nutMap = new NutMap();
					nutMap.put("equipment_manufacturer_id", rs.getString("equipment_manufacturer_id"));
					nutMap.put("equipment_manufacturer_name", rs.getString("equipment_manufacturer_name"));
					list.add(nutMap);
				}
				return list;
			}
		});
		dao().execute(find_sql);
		return find_sql.getList(NutMap.class);
	}
	
	
	public QueryResult findManufacturerEquipmentPageList(NutMap nutMap){
		List <DcManufacturerEquipment> page_list = null;
		Pager pager = null;
		Criteria criteria = Cnd.NEW();
		criteria.where().and("del_flag", "=", false);
		if(nutMap.get("manufacturer_name") != null){
			criteria.where().and("equipment_manufacturer_name", "like", "%"+nutMap.get("manufacturer_name")+"%");
		}
		if(nutMap.get("equipment_id") != null){
			criteria.where().and("equipment_id", "=", nutMap.get("equipment_id"));
		}
		if(nutMap.get("manufacturer_id") != null){
			criteria.where().and("equipment_manufacturer_id", "=", nutMap.get("manufacturer_id"));
		}
		if(nutMap.get("manufacturer_equipment_name") != null){
			criteria.where().and("manufacturer_equipment_name", "like", "%"+nutMap.get("manufacturer_equipment_name")+"%");
		}
		criteria.getOrderBy().desc("create_time");
		if(nutMap.get("page_num") != null && nutMap.get("page_size") != null){
			pager = new Pager(nutMap.getInt("page_num"), nutMap.getInt("page_size"));
			pager.setRecordCount(count(criteria));
			page_list = query(criteria, pager);
		}else{
			page_list = query(criteria);
		}
		return new QueryResult(page_list,pager);
	}
	
	public int updateManufacturerEquipment(DcManufacturerEquipment dcManufacturerEquipment){
		if(dcManufacturerEquipment == null){
			return 0;
		}
		int i = 0;
		String id = dcManufacturerEquipment.getId();
		if(StringUtils.isNotBlank(id)){
			DcManufacturerEquipment update_obj = fetch(id);
			update_obj.setManufacturer_equipment_name(dcManufacturerEquipment.getManufacturer_equipment_name());
			update_obj.setEquipment_manufacturer_name(dcManufacturerEquipment.getEquipment_manufacturer_name());
			update_obj.setEquipment_manufacturer_id(dcManufacturerEquipment.getEquipment_manufacturer_id());
			update_obj.setEquipment_power(dcManufacturerEquipment.getEquipment_power());
			update_obj.setEquipment_price(dcManufacturerEquipment.getEquipment_price());
			update_obj.setEquipment_model(dcManufacturerEquipment.getEquipment_model());
			update_obj.setUpdate_time(CommonDateUtil.getDateTime());
			i = update(update_obj);
		}else{
			dcManufacturerEquipment.setCreate_time(CommonDateUtil.getDateTime());
			Object object = insert(dcManufacturerEquipment);
			if(object != null){
				i = 1;
			}
		}
		return i;
	}
	
	public int findGyEquipmentListNum(NutMap param){
		StringBuffer find_sql = new StringBuffer(""
				+ " select count(dme.id) num "
				+ " from "
				+ " dc_equipment de , dc_equipment_children_type dect , dc_manufacturer_equipment dme , dc_equipment_manufacturer dem ,"
				+ " dc_equipment_type det "
				+ " where dme.equipment_manufacturer_id = dem.id and dme.equipment_id = de.id and de.equipment_children_type_id = dect.id "
				+ " and dect.equipment_type_id = det.id "
				+ " and de.del_flag = '0' and dect.del_flag = '0' and dme.del_flag = '0' and dem.del_flag = '0' and det.del_flag = '0'"
				+ " and det.equipment_type_name like '%工艺设备%' ");
		if(param.get("equipment_name") != null){
			find_sql.append(" and manufacturer_equipment_name like @equipment_name");
		}
		if(param.get("equipment_children_type_id") != null){
			find_sql.append(" and equipment_children_type_id = @equipment_children_type_id ");
		}
		find_sql.append(" order by dem.equipment_manufacturer_name asc");
		Sql sql = Sqls.create(find_sql.toString());
		if(param.get("equipment_name") != null){
			sql.params().set("equipment_name", "%"+param.getString("equipment_name")+"%");
		}
		if(param.get("equipment_children_type_id") != null){
			sql.params().set("equipment_children_type_id", param.get("equipment_children_type_id"));
		}
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				int i = 0;
				while (rs.next()) {
					i = rs.getInt("num");
				}
				return i;
			}
		});
		dao().execute(sql);
		return sql.getInt();
	}
	
	
	//工艺设备使用场所
	public List findEquipmentTypeList(){
		List <NutMap> result_list = null;
		String find_sql = " select dect.* "
				+ " from dc_equipment_children_type dect inner join dc_equipment_type det "
				+ " on dect.equipment_type_id = det.id and dect.del_flag = '0' and det.del_flag = '0' "
				+ " where det.equipment_type_name like '%工艺设备%' ";
		Sql sql = Sqls.create(find_sql);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List <NutMap> list = new ArrayList();
				while (rs.next()) {
					NutMap map = new NutMap();
					ResultSetMetaData metaData = rs.getMetaData();
					int count = metaData.getColumnCount();
					for (int i = count; i > 0; i--) {
						String filed = metaData.getColumnName(i);
						String value = rs.getString(filed);
						map.put(filed, value);
					}
					list.add(map);
				}
				return list;
			}
		});
		dao().execute(sql);
		result_list = sql.getList(NutMap.class);
		return result_list;
	}
	
	
	//工艺设备资料库
	public QueryResult findGyEquipmentList(NutMap param){
		List <NutMap> list = null;
		Pager pager = null;
		StringBuffer find_sql = new StringBuffer(""
				+ " select dme.id , dme.manufacturer_equipment_name , dme.equipment_price , "
				+ " dme.equipment_model  , "
				+ " dme.equipment_power , dem.equipment_manufacturer_name , "
				+ " dect.id equipment_children_type_id, dect.equipment_children_type_name "
				+ " from "
				+ " dc_equipment de , dc_equipment_children_type dect , dc_manufacturer_equipment dme , dc_equipment_manufacturer dem ,"
				+ " dc_equipment_type det "
				+ " where dme.equipment_manufacturer_id = dem.id and dme.equipment_id = de.id and de.equipment_children_type_id = dect.id "
				+ " and dect.equipment_type_id = det.id "
				+ " and de.del_flag = '0' and dect.del_flag = '0' and dme.del_flag = '0' and dem.del_flag = '0' and det.del_flag = '0'"
				+ " and det.equipment_type_name like '%工艺设备%' ");
		if(param.get("equipment_name") != null){
			find_sql.append(" and manufacturer_equipment_name like @equipment_name");
		}
		if(param.get("equipment_children_type_id") != null){
			find_sql.append(" and equipment_children_type_id = @equipment_children_type_id ");
		}
		find_sql.append(" order by dect.equipment_children_type_name");
		Sql sql = Sqls.create(find_sql.toString());
		if(param.get("equipment_name") != null){
			sql.params().set("equipment_name", "%"+param.getString("equipment_name")+"%");
		}
		if(param.get("equipment_children_type_id") != null){
			sql.params().set("equipment_children_type_id", param.get("equipment_children_type_id"));
		}
		if(param.get("page_num") != null && param.get("page_size") != null){
			pager = new Pager(param.getInt("page_num"),param.getInt("page_size"));
			pager.setRecordCount(findGyEquipmentListNum(param));
			sql.setPager(pager);
		}
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List <NutMap> list = new ArrayList();
				while (rs.next()) {
					NutMap map = new NutMap();
					ResultSetMetaData metaData = rs.getMetaData();
					int count = metaData.getColumnCount();
					for (int i = count; i > 0; i--) {
						String filed = metaData.getColumnName(i);
						String value = rs.getString(filed);
						map.put(filed, value);
					}
					list.add(map);
				}
				return list;
			}
		});
		dao().execute(sql);
		list = sql.getList(NutMap.class);
		return new QueryResult(list, pager);
	}


	public int findBulletFacilityListNum(NutMap param){
		StringBuffer find_sql = new StringBuffer(" select count(dci.id) num  from  dc_crh_info dci  where dci.del_flag='0' ");
		if(param.get("crh_info_name") != null){
			find_sql.append(" and dci.crh_info_name like @crh_info_name ");
		}
		find_sql.append("   order by dci.crh_info_name asc ");
		Sql sql = Sqls.create(find_sql.toString());

		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				int i = 0;
				while (rs.next()) {
					i = rs.getInt("num");
				}
				return i;
			}
		});
		dao().execute(sql);
		return sql.getInt();
	}


	//动车设施信息库
	public QueryResult findBulletFacilityList(NutMap param){
		List <NutMap> list = null;
		Pager pager = null;
		StringBuffer find_sql = new StringBuffer("  select dci.id,dci.crh_info_name , dci.crh_info_type ,dci.crh_info_size , dci.crh_info_city , dci.crh_info_department , "
				+" dci.crh_info_longitude , dci.crh_info_latitude, dci.crh_info_attach  "
				+"  from  dc_crh_info dci where dci.del_flag='0'  ");
		if(param.get("crh_info_name") != null){
			find_sql.append(" and crh_info_name like @crh_info_name");
		}
		find_sql.append(" order by dci.crh_info_name asc ");
		Sql sql = Sqls.create(find_sql.toString());
		if(param.get("crh_info_name") != null){
			sql.params().set("crh_info_name", "%"+param.getString("crh_info_name")+"%");
		}
		if(param.get("page_num") != null && param.get("page_size") != null){
			pager = new Pager(param.getInt("page_num"),param.getInt("page_size"));
			pager.setRecordCount(findBulletFacilityListNum(param));
			sql.setPager(pager);
		}
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List <NutMap> list = new ArrayList();
				while (rs.next()) {
					NutMap map = new NutMap();
					ResultSetMetaData metaData = rs.getMetaData();
					int count = metaData.getColumnCount();
					for (int i = count; i > 0; i--) {
						String filed = metaData.getColumnName(i);
						String value = rs.getString(filed);
						map.put(filed, StringUtil.removeTrim(value));
					}
					list.add(map);
				}
				return list;
			}
		});
		dao().execute(sql);
		list = sql.getList(NutMap.class);
		return new QueryResult(list, pager);
	}
}
